<?xml version="1.0"?>

<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:xsp="http://www.apache.org/1999/XSP/Core"
  xmlns:connection="http://www.plenix.com/2000/Connection"
>
  <xsl:template match="xsp:page">
    <xsp:page>
      <xsl:copy>
        <xsl:apply-templates select="@*"/>
      </xsl:copy>

      <xsp:structure>
        <xsp:include>java.sql.*</xsp:include>
        <xsp:include>com.prism.*</xsp:include>
      </xsp:structure>

      <xsp:logic>
        protected static com.prism.ConnectionWrapper xspConn = new com.prism.ConnectionWrapper();

        protected Element executeQuery(
          HttpServletRequest request,
          String statementId,
          String statementText,
          Document factory
        )
          throws SQLException
        {
          DBConnection dbConn = null;
          try {
            dbConn = xspConn.getConnection(request);
            Statement statement = dbConn.sqlconn.createStatement();
            ResultSet resultSet = statement.executeQuery(statementText);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            
            Element rowSet = factory.createElement("connection:rowset");
            rowSet.setAttribute("id", statementId);

            for (int i = 0; resultSet.next(); i++) {
              Element row = factory.createElement("connection:row");
              rowSet.appendChild(row);
              row.setAttribute("sequence", String.valueOf(i + 1));

              for (int j = 0; j &lt; columnCount; j++) {
                String value = resultSet.getString(j + 1);

                Element element = factory.createElement(metaData.getColumnLabel(j + 1).toLowerCase());
                row.appendChild(element);

                if (value != null) {
                  element.appendChild(
                    factory.createTextNode(value)
                  );
                }
              }
            }

            resultSet.close();
            statement.close();

            return rowSet;
          } finally {
            if (dbConn != null) {
              xspConn.freeConnection(request,dbConn);
            }
          }
        }

        protected Element executeUpdate(
                        HttpServletRequest request,
                        String statementText,
                        Document factory)
          throws SQLException
        {
          DBConnection dbConn = null;
    
          try {
            dbConn = xspConn.getConnection(request);
            Statement statement = dbConn.sqlconn.createStatement();
            int rowCount = statement.executeUpdate(statementText);
            Element result = factory.createElement("connection:result");
            result.setAttribute("row-count", String.valueOf(rowCount));
            return result;
          } catch (Exception e) {
            Element result = factory.createElement("connection:result");
            result.setAttribute("row-count", "-1");
            result.appendChild(factory.createTextNode(e.getMessage()));
            return result;
          } finally {
            if (dbConn != null) {
              if (!dbConn.connInfo.txEnable) {
                  // if the transaction is not enable, make the modifications.
                  dbConn.sqlconn.commit();
              }
              xspConn.freeConnection(request,dbConn);
            }
          }
        }

        protected Element executeCommit(
                        HttpServletRequest request,
                        Document factory)
          throws SQLException
        {
          DBConnection dbConn = null;
    
          try {
            dbConn = xspConn.getConnection(request);
            Element result = factory.createElement("connection:result");
            result.setAttribute("row-count", "1");
            return result;
          } catch (Exception e) {
            Element result = factory.createElement("connection:result");
            result.setAttribute("row-count", "1");
            result.appendChild(factory.createTextNode(e.getMessage()));
            return result;
          } finally {
            if (dbConn != null) {
              if (!dbConn.connInfo.txEnable) {
                  // if the transaction is not enable, make the modifications.
                  dbConn.sqlconn.commit();
              }
              xspConn.freeConnection(request,dbConn);
            }
          }
        }
      </xsp:logic>

      <xsl:apply-templates/>
    </xsp:page>
  </xsl:template>

  <xsl:template match="connection:execute-query">
    <xsl:variable name="id">
      <xsl:choose>
        <xsl:when test="@id">"<xsl:value-of select="@id"/>"</xsl:when>
        <xsl:when test="connection:id">
          <xsl:call-template name="get-nested-content">
            <xsl:with-param name="content" select="connection:id"/>
          </xsl:call-template>
        </xsl:when>
      </xsl:choose>
    </xsl:variable>

    <xsl:variable name="statement">
      <xsl:choose>
        <xsl:when test="@statement">"<xsl:value-of select="@statement"/>"</xsl:when>
        <xsl:when test="connection:statement">
          <xsl:call-template name="get-nested-string">
            <xsl:with-param name="content" select="connection:statement"/>
          </xsl:call-template>
        </xsl:when>
      </xsl:choose>
    </xsl:variable>

    <xsp:logic>
      xspCurrentNode.appendChild(
        executeQuery(
          request,
          String.valueOf(<xsl:copy-of select="$id"/>),
          String.valueOf(<xsl:copy-of select="$statement"/>),
          document
        )
      );
    </xsp:logic>
  </xsl:template>

  <xsl:template match="connection:execute-update">
    <xsl:variable name="statement">
      <xsl:choose>
        <xsl:when test="@statement">"<xsl:value-of select="@statement"/>"</xsl:when>
        <xsl:when test="connection:statement">
          <xsl:call-template name="get-nested-string">
            <xsl:with-param name="content" select="connection:statement"/>
          </xsl:call-template>
        </xsl:when>
      </xsl:choose>
    </xsl:variable>

    <xsp:logic>
      xspCurrentNode.appendChild(
        executeUpdate(
          request,
          String.valueOf(<xsl:copy-of select="$statement"/>),
          document
        )
      );
    </xsp:logic>

  </xsl:template>

  <xsl:template match="connection:execute-commit">
    <xsp:logic>
      xspCurrentNode.appendChild(
        executeCommit(
          request,
          document
        )
      );
    </xsp:logic>

  </xsl:template>

  <xsl:template name="get-nested-content">
    <xsl:param name="content"/>
    <xsl:choose>
      <xsl:when test="$content/*">
        <xsl:apply-templates select="$content/*"/>
      </xsl:when>
      <xsl:otherwise>"<xsl:value-of select="$content"/>"</xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <xsl:template name="get-nested-string">
    <xsl:param name="content"/>
    <xsl:choose>
      <xsl:when test="$content/*">
        ""
        <xsl:for-each select="$content/node()">
          <xsl:choose>
            <xsl:when test="name(.)">
              + <xsl:apply-templates select="."/>
            </xsl:when>
            <xsl:otherwise> <!-- text()? -->
              + "<xsl:value-of select="normalize-space(translate(.,'&#9;&#10;&#13;', '   '))"/> "
            </xsl:otherwise>
          </xsl:choose>
        </xsl:for-each>
      </xsl:when>
      <xsl:otherwise>
        "<xsl:value-of select="normalize-space(translate(.,'&#9;&#10;&#13;', '   '))"/> "
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>


  <xsl:template match="@*|*|text()|processing-instruction()">
    <xsl:copy>
      <xsl:apply-templates select="@*|*|text()|processing-instruction()"/>
    </xsl:copy>
  </xsl:template>

</xsl:stylesheet>
